﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace childcare
{
    public partial class frmlop : Form
    {

        SqlConnection con = new SqlConnection();
        private DataTable dt = new DataTable();
        private SqlDataAdapter da = new SqlDataAdapter();
        private Boolean kt;
        public frmlop()
        {
            InitializeComponent();
        }

        private void lblma_Click(object sender, EventArgs e)
        {

        }

        private void textBox3_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void btnsua_Click(object sender, EventArgs e)
        {
            test();
            if (kt == true)
            {
                SqlCommand commandupdate = new SqlCommand();
                commandupdate.Connection = con;
                commandupdate.CommandType = CommandType.Text;
                commandupdate.CommandText = @"update class set age = @age,classname = @classname where CL_ID = @CL_ID";
                SqlParameter pram = commandupdate.Parameters.Add("@CL_ID", SqlDbType.VarChar, 6);
                pram.Value = txtmalop.Text;
                pram = commandupdate.Parameters.Add("@age", SqlDbType.VarChar, 6);
                pram.Value = txtdotuoi.Text;
                pram = commandupdate.Parameters.Add("@classname", SqlDbType.NVarChar, 30);
                pram.Value = txttenlop.Text;
                con.Open();
                commandupdate.ExecuteNonQuery();
                con.Close();
                dgv_lophoc.DataSource = connectdatabase.ex("select * from class");
                MessageBox.Show("Bạn đã Sửa thành công!", "Thông Báo", MessageBoxButtons.OK);
                databinding();
            }
        }

        private void btnthem_Click(object sender, EventArgs e)
        {
            test();
            if (kt == true)
            {
                try
                {
                    SqlCommand commandinsert = new SqlCommand();
                    commandinsert.Connection = con;
                    commandinsert.CommandType = CommandType.Text;
                    commandinsert.CommandText = "insert class (CL_ID,age,classname) values(@CL_ID,@age, @classname)";
                    SqlParameter pram = commandinsert.Parameters.Add("@CL_ID", SqlDbType.VarChar, 6);
                    pram.Value = txtmalop.Text;
                    pram = commandinsert.Parameters.Add("@age", SqlDbType.VarChar, 6);
                    pram.Value = txtdotuoi.Text;
                    pram = commandinsert.Parameters.Add("@classname", SqlDbType.NVarChar,30);
                    pram.Value = txttenlop.Text;
                    con.Open();
                    commandinsert.ExecuteNonQuery();
                    con.Close();
                    dgv_lophoc.DataSource = connectdatabase.ex("select * from class");
                    MessageBox.Show("Bạn đã thêm thành công!", "Thông Báo", MessageBoxButtons.OK);
                    databinding();
                }
                catch
                {
                    MessageBox.Show("Mã lớp này đã tồn tại! mời bạn nhập lại mã lớp khác", "Thông Báo", MessageBoxButtons.OK);


                }

            }
        }

        private bool test()
        {
            kt = true;
            if (txtmalop.Text == "")
            {
                MessageBox.Show("Bạn phải nhập lớp", "THÔNG BÁO", MessageBoxButtons.OK);
                kt = false;
                txtmalop.Focus();
            }
            return kt;
        }

        private void databinding()
        {
            txtmalop.DataBindings.Clear();
            txtmalop.DataBindings.Add("Text",dgv_lophoc.DataSource,"CL_ID");
            txtdotuoi.DataBindings.Clear();
            txtdotuoi.DataBindings.Add("Text", dgv_lophoc.DataSource, "age");
            txttenlop.DataBindings.Clear();
            txttenlop.DataBindings.Add("Text", dgv_lophoc.DataSource, "classname");
        }
        private void frmlop_Load(object sender, EventArgs e)
        {
            string connect = @"Data Source=BLACK-PC\SQLEXPRESS;Initial Catalog=Childcare;Integrated Security=True";
            con = new SqlConnection(connect);
            connectdatabase.Openconnect();
            dgv_lophoc.DataSource = connectdatabase.ex("select * from class");
            databinding();
            
        }

        private void btnthoat_Click(object sender, EventArgs e)
        {
            this.Close();
            frmguest _frmguest = new frmguest();
            _frmguest.Show();
        }

        private void btnhuy_Click(object sender, EventArgs e)
        {
            txtmalop.Clear();
            txtdotuoi.Clear();
            txttenlop.Clear();
        }

        private void btnxoa_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có thực sự muốn xóa không ?", "XÓA", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                SqlCommand commandelete = new SqlCommand();
                commandelete.Connection = con;
                commandelete.CommandType = CommandType.Text;
                commandelete.CommandText = @"delete from hoc_phi where CL_ID = @CL_ID";
                SqlParameter pram = commandelete.Parameters.Add("@CL_ID", SqlDbType.VarChar, 6);
                pram.Value = txtmalop.Text;                       
                con.Open();
                commandelete.ExecuteNonQuery();
                commandelete.CommandText = @"delete from children where CL_ID = @CL_ID";
                commandelete.ExecuteNonQuery();
                commandelete.CommandText = @"delete from xeplop where CL_ID = @CL_ID";
                commandelete.ExecuteNonQuery();
                commandelete.CommandText = @"delete from class where CL_ID = @CL_ID";
                commandelete.ExecuteNonQuery();    
                con.Close();
                dgv_lophoc.DataSource = connectdatabase.ex("select * from class");
                MessageBox.Show("Bạn đã Xóa thành công!", "Thông Báo", MessageBoxButtons.OK);
                databinding();
            }
        }

        private void label5_Click(object sender, EventArgs e)
        {

        }

        private void btntklop_Click(object sender, EventArgs e)
        {
            dt.Clear();
            SqlCommand commandsearch = new SqlCommand();
            commandsearch.Connection = con;
            commandsearch.CommandType = CommandType.Text;
            commandsearch.CommandText = "select * from class where CL_ID=@CL_ID";
            SqlParameter pram = commandsearch.Parameters.Add("@CL_ID", SqlDbType.VarChar, 6);
            pram.Value = txttklop.Text;
            da.SelectCommand = commandsearch;
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                dgv_lophoc.DataSource = dt;
            }
            else
            {
                MessageBox.Show("Không tìm thấy Lớp này!", "THÔNG BÁO", MessageBoxButtons.OK);
            }
            databinding();
        }

        private void dgv_lophoc_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}
